<?php

/**
 * This is the model class for table "{{issue}}".
 *
 * The followings are the available columns in table '{{issue}}':
 * @property integer $ID
 * @property integer $userID
 * @property integer $attempt_time
 * @property integer $gradeID
 * @property integer $subjectID
 * @property integer $emergency
 * @property integer $quantity_object
 * @property integer $estimate_idle_time
 * @property integer $level_complex
 * @property string $content
 * @property integer $status
 * @property integer $checker
 * @property integer $resolver
 * @property integer $createddate
 * @property integer $modifieddate
 * @property integer $deleted
 * @property integer $notify_date
 */
class Issue extends CActiveRecord
{
		public $image;
	  public $captcha;
	/**
	 * Returns the static model of the specified AR class.
	 * @param string $className active record class name.
	 * @return Issue the static model class
	 */
	public static function model($className=__CLASS__)
	{
		return parent::model($className);
	}

	/**
	 * @return string the associated database table name
	 */
	public function tableName()
	{
		return '{{issue}}';
	}

	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(
			array('userID, attempt_time, gradeID, subjectID, emergency, quantity_object', 'required'),
			//array('image','ext.Validate.MyFileValidator','allowEmpty'=>true,'types'=>'jpg,jpeg,png,gif',),
			array('image','file','allowEmpty'=>true,'types'=>'jpg,jpeg,png,gif',),
			array('userID, attempt_time, gradeID, subjectID, emergency, quantity_object, estimate_idle_time, level_complex, status, checker, resolver, createddate, modifieddate, deleted, notify_date', 'numerical', 'integerOnly'=>true),
			array('captcha','required'),
		  array('captcha','captcha', 'allowEmpty'=>!CCaptcha::checkRequirements()),
			// The following rule is used by search().
			// Please remove those attributes that should not be searched.
			array('ID, userID, attempt_time, gradeID, subjectID, emergency, quantity_object, estimate_idle_time, level_complex, content, status, checker, resolver, createddate, modifieddate, deleted, notify_date', 'safe', 'on'=>'search'),
		);
	}

	/**
	 * @return array relational rules.
	 */
	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
		);
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
			return array(
			'ID' => 'ID',
			'userID' => 'User',
			'attempt_time' => 'Số lần giải',
			'gradeID' => 'Lớp',
			'subjectID' => 'Môn học',
			'emergency' => 'Mức độ yêu cầu',
			'estimate_idle_time' => 'Thời gian ước lượng',
			'level_complex' => 'Độ phức tạp',
			'content' => 'Nội dung',
			'status' => 'Trạng thái',
			'resolver' => 'Người giải',
			'createddate' => 'Ngày tạo',
			'modifieddate' => 'Ngày update',
			'deleted' => 'Deleted',
			'notify_date' => 'Thời hạn',		
			'quantity_object' => 'Số lượng',
			'image'=>'File',			
			'captcha'=>'Mã xác nhận'
		);
	}

	
  protected function beforeSave() {
		if($this->isNewRecord) {
			$this->attempt_time = 0;						
			$this->level_complex = 1;					
			$this->createddate =time();	
		}
			$this->modifieddate = time();
		return parent::beforeSave();
	}
	
	/**
	 * get quantity for submit issue of user
	 */
	public function getQuantity() {
	  $array = array();
	  for($i=1;$i<=5;$i++) {
	    $array[$i] = "$i bài";
	  }
	  return $array;
	}
	//getListSort used in assigneditor page
	public function getListSort() {
	 	$array = array();	 
	  $array['emergency'] = "Theo muc do uu tien";	
	  $array['resolved'] = "Da giai roi";	  	
	  $array['not_resolve'] = "Chua giai";
	   
	  return $array;
	}
	/**
	 * get level of emergency
	 * @author khatv
	 */
	public function getEmergency() {
	  $array = array();	 
	  $array[1] = "Bình thường";	
	  $array[2] = "Trung bình";	  	
	  $array[3] = "Khấp cấp"; 
	  return $array;
	}
	
	public function getAllAnswers($issueID)
	{
		$array = array();
		$answers = Answer::model()->findAllByAttributes(array('issueID'=>$issueID,'deleted'=>0));
		if (!empty($answers))
    {
      foreach ($answers as $r) {
        $array[$r['ID']] = $r['ID'];	      
      }
    }
    return $array;	
	}
	
	public function getAnswerID($issueID,$thread_number)
	{	
		$answerID =-1;		
		$answers = Answer::model()->findByAttributes(array('issueID'=>$issueID,'thread_number'=>$thread_number));				
		if (!empty($answers))
    {
	       $answerID= $answers->ID; 
    }    
    return $answerID;
	}
	
	public function getQuestionID($issueID,$thread_number)
	{	
		$questionID =-1;		
		$question = Question::model()->findByAttributes(array('issueID'=>$issueID,'thread_number'=>$thread_number));				
		if (!empty($question))
    {
	       $questionID= $question->ID; 
    }    
    return $questionID;
	}
	
	public function getResolveStatus($answerID)
	{	
		$status ="Không tìm thấy bài giải";	
		$answer = Answer::model()->findByAttributes(array('ID'=>$answerID));
		if (!empty($answer))
    {
    	$resolve_status =$answer->resolve_status;
      if($resolve_status==30)
      {
      	$status ="Chưa phân công cho resolver";	
      } else if($resolve_status==31)
      {
      	$status ="Đã phân công cho resolver";	
      } else if($resolve_status==32)
      {
      	$status ="Đã giải";	
      } else if($resolve_status==33)
      {
      	$status ="Không thể giải";	
      } else if($resolve_status==34)
      {
      	$status ="Đề bài không hợp lệ";	
      } else if($resolve_status==35)
      {
      	$status ="Hết giờ để giải";	
      } else if($resolve_status==36)
      {
      	$status ="Phân công lại";	
      } else if($resolve_status==37)
      {
      	$status ="Đã xác nhận";	
      } else
      {
      	$status ="Invalid status";	
      }
    }
    return $status;
	}
	
	public function getStatus($objecType,$objectID,$type)
	{	
		$object_status ="Unknown";	
		if($objecType=='question')
		{
			$object =	Question::model()->findByAttributes(array('ID'=>$objectID));
			if (!empty($object))
	    { 	      
	      $question_status = $object->status;	     
	      $status = Status::model()->findByAttributes(array('statusID'=>$question_status));	    	      
   	 	}
		}
		else if($objecType='answer')
		{
			$object = Answer::model()->findByAttributes(array('ID'=>$objectID));
					
			if (!empty($object))
	    { 
	    	if($type=='resolve_status')
	    	{
	    		$answer_status =$object->resolve_status;		    	
	    	}
	    	else if($type=='edit_status')
	    	{
	    		$answer_status =$object->edit_status;	
	    	}
	    		    	      
	      $status =Status::model()->findByAttributes(array('statusID'=>$answer_status));		         
   	 	}
		}		
		
		if(!empty($status))
		{
			$object_status = $status->status;
		}
		
    return $object_status;
	}

	/**
	 * Get AnswerIDs
	 * @param unknown_type $issueID
	 */
	public function getAnswerIDs($issueID)
	{
		$array = array();
		$answers = Yii::app()->db->createCommand()
						->select('th.issueID,th.thread_number number')
						->from('tbl_issue as is')
						->join('tbl_answer as th','is.ID=th.issueID')						
						->where("is.ID=$issueID")					
						->queryAll();
				
		if (!empty($answers))
    {
      foreach ($answers as $a) {
        $array[$a['number']] = $a['number'];	       
      }
    }
    return $array;
	}
	
	public function getResolveTime($issueID)
	{
		$array = array();
		$answers = Yii::app()->db->createCommand()
						->select('th.issueID,th.thread_number number')
						->from('tbl_issue as is')
						->join('tbl_answer as th','is.ID=th.issueID')						
						->where("is.ID=$issueID")					
						->queryAll();
				
		if (!empty($answers))
    {
      foreach ($answers as $a) {
        $array[$a['number']] = $a['number'];	       
      }
    }
    return $array;
	}
	
	/**
	 * Get answer of resolver
	 * Enter description here ...
	 * @param unknown_type $issueID
	 * @param unknown_type $userID
	 */
	public function getAnswersOfResolver($issueID,$userID)
	{
		$array = array();
		$answers = Yii::app()->db->createCommand()
						->select('an.issueID, an.thread_number number')
						->from('tbl_issue as is')
						->rightJoin('tbl_answer as an','is.ID=an.issueID')					
						->where("is.ID=$issueID AND an.resolver=$userID")
						->queryAll();
		
		if (!empty($answers))
    {
      foreach ($answers as $a) {
        $array[$a['number']] = $a['number'];	       
      }
    }
    return $array;	
	}
	
	/**
	 * Get answer of resolver
	 * @param unknown_type $issueID
	 * @param unknown_type $userID
	 */
	public function getAnswers($issueID,$userID)
	{
		$array = array();
		$answers = Yii::app()->db->createCommand()
						->select('an.*')
						->from('tbl_issue as is')
						->rightJoin('tbl_answer as an','is.ID=an.issueID')					
						->where("is.ID=$issueID AND an.resolver=$userID")
						->queryAll();
						
		if (!empty($answers))
    {
      foreach ($answers as $a) {
        $array[$a['thread_number']] = $a;	   
      }
    }   
    return $array;	
	}
	
	public function getAnswerForEdit($issueID,$userID)
	{
		$array = array();
		$answers = Yii::app()->db->createCommand()
						->select('an.*')
						->from('tbl_issue as is')
						->rightJoin('tbl_answer as an','is.ID=an.issueID')					
						->where("is.ID=$issueID AND an.editor=$userID")
						->queryAll();
						
		if (!empty($answers))
    {
      foreach ($answers as $a) {
        $array[$a['thread_number']] = $a;	   
      }
    }   
    return $array;	
	}
	
	public function getQuestionThread($issueID,$num)
	{	
		$answer = Yii::app()->db->createCommand()
						->select('an.*')
						->from('tbl_issue as is')
						->rightJoin('tbl_question as an','is.ID=an.issueID')					
						->where("is.ID=$issueID AND an.thread_number=$num AND an.status!=49 ")
				    ->queryRow();     
		return $answer;
	}
	
	/**
	 * Get answer of issue
	 * @param unknown_type $issueID
	 * @param unknown_type $num
	 */
	public function getAnswerThread($issueID,$num)
	{	
		$answer = Yii::app()->db->createCommand()
						->select('an.*')
						->from('tbl_issue as is')
						->rightJoin('tbl_answer as an','is.ID=an.issueID')					
						->where("is.ID=$issueID AND an.thread_number=$num")
				    ->queryRow();     
		return $answer;
	}
	
	public function getAnswerThread1($issueID,$num)
	{	
		$answer = Yii::app()->db->createCommand()
						->select('an.*')
						->from('tbl_issue as is')
						->rightJoin('tbl_answer as an','is.ID=an.issueID')					
						->where("is.ID=$issueID AND an.thread_number=$num AND an.edit_status!=49 " )
				    ->queryRow();     
		return $answer;
	}
	
	
	/**
	 * Get Available Users 	 * 
	 * @param int $role
	 */
	public function getUsers($role, $status = array(1))
	{
		$array = array();
		$stt = implode(",", $status);
		$users = Yii::app()->db->createCommand()
            ->select('u.*, up.fullname')
            ->from('tbl_user as u')
            ->join('tbl_user_profile as up', 'up.userID=u.ID')
            ->join('tbl_user_role as ur', 'ur.userID = u.ID')
            ->join('tbl_user_status as us','us.userID=u.ID')
            ->where('ur.roleID='.$role.' AND us.statusID IN ('.$stt.')')
            ->order('us.statusID,up.fullname')
            ->queryAll(); 
    if (!empty($users))
    {
      foreach ($users as $r) {
        $array[$r['ID']] = $r['fullname'];	       
      }
    }
    return $array;
	}
	
//function getBalance
	public function getIssue($userID) {
		$issue = $this->model()->findByAttributes(array('userID'=>$userID)); 
		return $issue;
	}
	
	/**
	 * Get Available Resolver
	 */
	public function getResolverList()
	{
	  $array = array();
		$resolver = Yii::app()->db->createCommand()
            ->select('u.*, up.fullname')
            ->from('tbl_user as u')
            ->join('tbl_user_profile as up', 'up.userID=u.ID')
            ->join('tbl_user_role as ur', 'ur.userID = u.ID')
            ->join('tbl_user_status as us','us.userID=u.ID')
            ->where('ur.roleID=3 AND us.statusID =1')
            ->order('up.fullname')
            ->queryAll(); 
    if (!empty($resolver))
    {
      foreach ($resolver as $r) {
        $array[$r['ID']] = $r['fullname'];	// cho nay lay ho ten dc khong e         
      }
    }
    return $array;
	}
	
	//function Get Available Checker
	public function getChecker()
	{
		$resolver = Yii::app()->db->createCommand()
            ->select('u.*')
            ->from('tbl_user as u')
            ->join('tbl_user_role as ur', 'ur.userID = u.ID')
            ->join('tbl_user_status as us','us.userID=u.ID')
            ->where('ur.roleID=3 AND us.statusID =1')
            ->queryRow(); 
            
    if (!empty($resolver))
    {
      return $resolver['ID'];	// cho nay lay ho ten dc khong e         
    }
    else
    {
    	return 0;
    }
	}
	
	/**
	 * Estimate time for resolve issue
	 * @author khatv
	 * @param unknown_type $emegency
	 */
	public function estimateTime($emegency)
	{
		$time =360;
		if($emegency==1)
		{
			$time=120;			
		}
		else if($emegency==2)
		{
			$time=240;	
		}
		
		return $time;
	}
	
	//function Get Available Checker
	public function isResolvedIssue($issueID)
	{
		$answers = Answer::model()->findAllByAttributes(array('issueID'=>$issueID));
		if(!empty($answers))
		{
			foreach ($answers as $as)
			{
				if($as->status==23)
				{
					return false;
				}
			}
		}
		return true;		
	}
	
	
	/**
	 * Retrieves a list of models based on the current search/filter conditions.
	 * @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
	 */
	public function search()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria=new CDbCriteria;
		$criteria->condition = "t.deleted=0";
		$criteria->compare('ID',$this->ID);
		$criteria->compare('userID',$this->userID);
		$criteria->compare('attempt_time',$this->attempt_time);
		$criteria->compare('gradeID',$this->gradeID);
		$criteria->compare('subjectID',$this->subjectID);
		$criteria->compare('emergency',$this->emergency);
		$criteria->compare('quantity_object',$this->quantity_object);
		$criteria->compare('estimate_idle_time',$this->estimate_idle_time);
		$criteria->compare('level_complex',$this->level_complex);
		$criteria->compare('content',$this->content,true);
		$criteria->compare('status',$this->status);
		$criteria->compare('checker',$this->checker);
		$criteria->compare('resolver',$this->resolver);
		$criteria->compare('createddate',$this->createddate);
		$criteria->compare('modifieddate',$this->modifieddate);
		$criteria->compare('deleted',$this->deleted);
		$criteria->compare('notify_date',$this->notify_date);

		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	/**
	 * assign for assign-issue page
	 * @author K.D
	 */
	public function assign($ID='')
	{
		//action for index, list all thread of each issue
	  $criteria=new CDbCriteria;
	  $criteria->condition = "t.deleted=0";
	  if($ID!="") {
	  	$criteria->addCondition("t.ID=$ID");
	  } 
	  	  
	   // action for editing 
	  if(Yii::app()->controller->action->id=="edit") {
	  	$criteria->together = true;
	  	$criteria->select = "t.*";
	  	$criteria->join = "RIGHT JOIN tbl_answer as th ON th.issueID=t.ID";	
	  	$criteria->addCondition("th.editor=".Yii::app()->user->id);
	  	$criteria->group = "t.ID";	  	
	  }	 
	  
	  //action for assign editor
	  if (Yii::app()->controller->action->id=="assigneditor") {
	  	$criteria->order = 't.emergency DESC'; //default se sort theo muc do uu tien
	  	if(isset($_GET['sort']) && !empty($_GET['sort'])) {
	  		//sort theo cai dropdown 
	  		if($_GET['sort']=='emergency') {
	  			$criteria->order = 't.emergency '.$_GET['sort_type'];
	  		}
	  	}
	  }	  
	  
	  return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
	/**
	 * Action for resolve
	 * Enter description here ...
	 * @param unknown_type $ID
	 */
	public function resolve($ID='')
	{
		//action for index, list all thread of each issue
	  $criteria=new CDbCriteria;
	  $criteria->condition = "t.deleted=0";
	  if($ID!="") {
	  	$criteria->addCondition("t.ID=$ID");
	  } 
	  
	  // action for resolve 
  	$criteria->together = true;
  	$criteria->select = "t.* , ans.expire_resolve_time as exp_time";
  	$criteria->join = "RIGHT JOIN tbl_answer ans ON ans.issueID=t.ID";
  	$criteria->addCondition("ans.resolver=".Yii::app()->user->id);
  	$criteria->group = "t.ID";
	   
	  return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}	
	
	
	/**
	 * Get issue of user
	 * @param unknown_type $ID
	 * @author khatv
	 */
	public function getIssues()
	{
		//action for index, list all thread of each issue
	  $criteria=new CDbCriteria;
	  $criteria->condition = "t.deleted=0";
	  $userID =Yii::app()->user->id;	
	  if($userID!="") {
	  	$criteria->addCondition("t.userID=$userID");
	  } 
	  
	  return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}
}